package com.blackcat.dynamicexcel.utils;

import com.blackcat.dynamicexcel.entity.*;
import com.blackcat.dynamicexcel.vo.ExcelCellData;
import com.blackcat.dynamicexcel.vo.RowData;
import com.blackcat.dynamicexcel.vo.RowDataSubObject;
import com.blackcat.dynamicexcel.vo.SheetData;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 描述 ：excel工具类
 * @author : zhangdahui
 * @since : 2022/10/11 10:01
 */
public class DynamicExcelUtil {

    /**
     * 判断是否指定字段
     * @author : zhanghui
     * @since : 2023/1/30 09:35
     * @param cell 字段信息对象
     * @param field 字段名称
     */
    public static boolean isField(ExcelCellData cell, String field){
        if (field.equals(cell.getFieldName()) && StringUtils.isNotBlank(cell.getCellValue())) {
            return true;
        }
        return false;
    }

    /**
     * 下载 Excel
     * @author : zhanghui
     * @since : 2023/1/10 09:37
     * @param fileName 文件名称
    */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            //设置浏览器响应头对应的Content-disposition
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            //编码
            response.setCharacterEncoding("UTF-8");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 获取 Excel 原始数据
     * @author : zhanghui
     * @since : 2023/1/29 16:34
     * @param excelTemplate 模板
     * @param workbook 文件
    */
    public static List<SheetData> getExcelSheetData(ExcelTemplate excelTemplate, Workbook workbook){
        List<SheetData> result = new ArrayList<>(16);
        if (CollectionUtils.isNotEmpty(excelTemplate.getSheets())){
            for (ExcelSheet excelSheet : excelTemplate.getSheets()) {
                // 表名为空为补充说明页,不获取该 sheet 数据
                if (excelSheet.isData()){
                    // 如果数据库没有配置模板，就不获取数据
                    if (CollectionUtils.isNotEmpty(excelSheet.getHeaders())){
                        // 获取 Excel sheet 数据
                        Sheet sheet = workbook.getSheetAt(excelSheet.getSheetIndex());
                        // 获取工作表中的行数
                        int rowNum = sheet.getPhysicalNumberOfRows();
                        // 声明 sheet 数据对象
                        SheetData sheetData = new SheetData();
                        // 声明 sheet 数据行 集合对象
                        List<RowData> dataList = new ArrayList<>(16);
                        // 遍历所有的正式数据，从表头所在行+1开始获取
                        for (int i = excelSheet.getHeaderRow() + 1; i < rowNum; i++) {
                            // 获取行数据
                            Row row = sheet.getRow(i);
                            if (row != null) {
                                Map<String, String> fields=new HashMap<>(16);
                                List<ExcelCellData> cells = new ArrayList<>(16);
                                for (ExcelHeader header : excelSheet.getHeaders()) {
                                    // 获取单元格数据
                                    Cell excelCell = row.getCell(header.getHeaderIndex());
                                    ExcelCellData cell = new ExcelCellData();
                                    BeanUtils.copyProperties(header,cell);
                                    // 判断单元格中数据的类型，根据类型进行赋值
                                    cell.setCellValue(DynamicExcelUtil.formatCell(excelCell));
                                    cell.setRepeat(header.isRepeatValid());
                                    cell.setRow(i);
                                    cell.setSheetName(excelSheet.getSheetName());
                                    if (StringUtils.isNotBlank(header.getTranslateTable())) {
                                        if (StringUtils.isNotBlank(header.getTranslateCode())) {// 通用码表翻译
                                            cell.setTranslateCode(header.getTranslateCode());
                                        }else {// 独立表翻译码
                                            cell.setTranslateCode(header.getTranslateTable());
                                        }
                                    }
                                    cell.setTableName(excelSheet.getTableName());
                                    cells.add(cell);
                                    fields.put(cell.getFieldName(), cell.getCellValue());
                                }
                                // 判断整行数据是否为空
                                boolean flag = false;
                                for (ExcelCellData cell : cells) {
                                    if (StringUtils.isNotBlank(cell.getCellValue()) && !"".equals(cell.getCellValue().trim())) {
                                        flag = true;
                                        break;
                                    }
                                }
                                if (flag){
                                    dataList.add(new RowData(i,cells,fields));
                                }
                            }
                        }
                        // 整页没有数据，不加入结果集合
                        if (CollectionUtils.isNotEmpty(dataList)){
                            BeanUtils.copyProperties(excelSheet,sheetData);
                            sheetData.setRowDataList(dataList);
                            result.add(sheetData);
                        }
                    }
                }
            }
        }
        return result;
    }

    /**
     * 处理子表数据合并
     * @author : zhanghui
     * @since : 2023/1/29 16:41
     * @param excelSheetData sheet 的数据
    */
    public static List<RowData> getExcelSheetHandleData(SheetData excelSheetData){
        List<RowData> handleDataList = new ArrayList<>(16);
        List<RowData> list = excelSheetData.getRowDataList();
        // 有子表数据
        if (StringUtils.isNotBlank(excelSheetData.getMergeField())){
            // 根据合并字段分组数据
            Map<String,List<RowData>> groupByMergeField=list.stream().collect(
                    Collectors.groupingBy(i->i.getFields().get(excelSheetData.getMergeField()))
            );
            for (Map.Entry<String, List<RowData>> entry : groupByMergeField.entrySet()) {
                List<RowData> groupByList= entry.getValue();
                RowData rowData = new RowData();
                // 主表数据
                List<ExcelCellData> masterFields = new ArrayList<>(16);
                groupByList.get(0).getCells().forEach(c->{
                    if (c.getMergeGroup() == null) {
                        masterFields.add(c);
                    }
                });
                rowData.setRow(groupByList.get(0).getCells().get(0).getRow());
                rowData.setCells(masterFields);
                List<RowDataSubObject> sublistList = new ArrayList<>(16);
                // 处理子表数据
                for (RowData subData : groupByList) {
                    Map<String, List<ExcelCellData>> groupByGroupId = subData.getCells().stream()
                            .filter(k -> k.getMergeGroup() != null && StringUtils.isNotBlank(k.getCellValue()))
                            .collect(Collectors.groupingBy(j -> String.valueOf(j.getMergeGroup())));
                    for (Map.Entry<String, List<ExcelCellData>> subGroupBy : groupByGroupId.entrySet()) {
                        RowDataSubObject subObject = new RowDataSubObject();
                        List<ExcelCellData> subCellDataList = new ArrayList<>(16);
                        subGroupBy.getValue().forEach(c->{
                            if (subGroupBy.getKey().equals(c.getMergeGroup()) && StringUtils.isNotBlank(c.getCellValue())) {
                                subCellDataList.add(c);
                            }
                        });
                        if (CollectionUtils.isNotEmpty(subCellDataList)){
                            subObject.setCells(subCellDataList);
                            subObject.setRow(subData.getCells().get(0).getRow());
                            subObject.setGroup(subGroupBy.getKey());
                            sublistList.add(subObject);
                        }
                    }
                }
                Map<String, List<RowDataSubObject>> subGroup = sublistList.stream()
                        .collect(Collectors.groupingBy(RowDataSubObject::getGroup));
                rowData.setGroupList(subGroup);
                rowData.setSheetName(excelSheetData.getSheetName());
                handleDataList.add(rowData);
            }
        }else{
            handleDataList = list;
        }
        return handleDataList;
    }


    /**
     * 得到Workbook对象
     * @author : zhanghui
     * @since : 2022/12/23 10:09
     * @param file excel
    */
    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //这样写  excel 能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is =file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }

    /**
     * 获取单元格内容
     * @author : zhanghui
     * @since : 2022/12/23 13:41
     * @param cell 单元格
    */
    public static String formatCell(Cell cell) {
        String value = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                // 数值型，0
                case NUMERIC:
                    // 默认日期读取出来是数字，判断是否是日期格式的数字
                    if(DateUtil.isCellDateFormatted(cell)){
                        // 读取的数字是日期，转换一下格式
                        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        Date date = cell.getDateCellValue();
                        value = dateFormat.format(date);
                    }else {// 不是日期直接赋值
                        value = String.valueOf(cell.getNumericCellValue());
                        // 解决1234.0  去掉后面的.0
                        if(!"".equals(value.trim())){
                            String[] item = value.split("[.]");
                            if(1 < item.length && "0".equals(item[1])){
                                value = item[0];
                            }
                        }
                    }
                    break;
                // 公式类型，2
                case FORMULA:
                    value = String.valueOf(cell.getCellFormula());
                    break;
                // 空值，3
                case BLANK:
                    // 错误，5
                case ERROR:
                    value = "";
                    break;
                // 布尔型，4
                case BOOLEAN:
                    value = "" + cell.getBooleanCellValue();
                    break;
                // 字符串型，1
                case STRING:
                default:
                    value = cell.getStringCellValue().trim();
            }
        }
        return value;
    }

    /**
     * 校验表头是否正确
     * @author : zhanghui
     * @since : 2022/12/23 13:43
     * @param row 表头所在行
     * @param excelHeaders 表头定义
    */
    public static boolean checkHeader(Row row, List<ExcelHeader> excelHeaders){
        boolean flag = true;
        if (row != null) {
            for (ExcelHeader header : excelHeaders) {
                Cell cell = row.getCell(header.getHeaderIndex());
                if (!DynamicExcelUtil.formatCell(cell).equals(header.getHeaderName())){
                    System.out.println("错误表头列定义："+ DynamicExcelUtil.formatCell(cell)+" Excel："+header.getHeaderName());
                    flag = false;
                }
            }
        }
        return flag;
    }



}
